package com.dao.database;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;
import java.util.Vector;

import org.apache.log4j.Logger;

import com.dao.database.exceptions.DataAccessException;


public class StockCompanyDao implements GeneralObjectDAO,SpecyficObjectDAO {
	
	private final static Logger LOGGER = Logger.getLogger(StockCompanyDao.class
			.getName());
	private String dbConfig = "config.txt";
	private Connection connection;
	PredictableObject predictableObject;
	public static String companyTickerListEoddata="www.eoddata.com";
	public static String companyHistoricalDataYahoo="www.yahoo.com";
	private String specificObjectName=StockCompany.TYPE;
	
	public StockCompanyDao(PredictableObject predictableObject) throws IOException,DataAccessException{
		this.predictableObject=predictableObject;
		try {
			LOGGER.trace("constructor: StockCompanyDao("+predictableObject+")");
			loadProperties(dbConfig);
		} catch (FileNotFoundException e) {
			LOGGER.error("Couldont open property file: " + dbConfig, e);
			throw e;
		} catch (IOException e) {
			LOGGER.error("Couldn't load properties", e);
			throw e;
		} catch (SQLException e) {
			LOGGER.error("Couldn't establish connection to the database",e);
			throw new DataAccessException("Couldn't establish connection to the database",e);
		}		
	}
	
	private void loadProperties(String dbFileName)throws FileNotFoundException,
	IOException, SQLException {
		LOGGER.trace("method: loadProperties("+dbFileName+")");
		FileReader fr1 = new FileReader(dbFileName);
		BufferedReader br1 = new BufferedReader(fr1);
		Properties prop1 = new Properties();
		prop1.load(br1);
		connection = DriverManager.getConnection(
				"jdbc:oracle:thin:@" + prop1.getProperty("host") + ":"
						+ prop1.getProperty("database"),
				prop1.getProperty("user"), prop1.getProperty("password"));
		
	}

	@Override
	public void batchCreateObjects(BufferedReader reader, String source)
			throws IOException, DataAccessException {
		LOGGER.trace("method: batchCreateStockCompany("+reader+","+source+")");
			String line;
			String sql = "Insert into stock_company(idCompany,idObject,ticker,name,country,address) "
					+ "values(stock_company_seq.nextval,?,?,?,?,?)";

			try {
				PreparedStatement stmt = connection.prepareStatement(sql);
				while ((line = reader.readLine()) != null) {
					if (source.contentEquals("www.eoddata.com")) {
						String[] tickerList = line.split(String.valueOf((char) 9));
						if (tickerList[0] != "Symbol") {
							stmt.setInt(1, predictableObject.getObjId());
							stmt.setString(2, tickerList[0].toUpperCase());
							stmt.setString(3, tickerList[1].toUpperCase());
							stmt.setString(4, null);
							stmt.setString(5, null);
							stmt.addBatch();
						}
					}
					
				}
				stmt.executeBatch();
				stmt.close();
			} catch (IOException e) {
				LOGGER.error("Coludn't read source:" + source, e);
				throw e;
			} catch (SQLException e) {
				LOGGER.error("Couldn't create or execute statement "+sql, e);
				throw new DataAccessException("Coudlnt create or execute batch statement"+sql,e);
			}
		
	}

	@Override
	public void deleteObject(int objId) throws DataAccessException {
		LOGGER.trace("method: deleteObject("+objId+")");
		String sql="delete from stock_company where idobject="+predictableObject.getObjId()+
				" and idcompany="+objId;
		try {
			Statement stmt=connection.createStatement();
			stmt.executeUpdate(sql);
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
		
	}
	@Override
	public void deleteAllObjects() throws DataAccessException {
		LOGGER.trace("method: deleteAllObjects()");
		String sql="delete from stock_company where idobject="+predictableObject.getObjId();
		try {
			Statement stmt=connection.createStatement();
			stmt.executeUpdate(sql);
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
		
	}

	@Override
	public StockCompany findObject(int objId) throws DataAccessException {
		LOGGER.trace("method: findObject("+objId+")");
		String sql="select * from stock_company where idobject="+predictableObject.getObjId()+" and idcompany='"+objId+"'";
		try {
			PreparedStatement stmt = connection.prepareStatement(sql);
			ResultSet rs=stmt.executeQuery();
			rs.next();
			StockCompany company= new StockCompany(predictableObject.getObjId(),predictableObject.getObjName(),predictableObject.getType(),predictableObject.getVariableMap(), rs.getInt("idcompany"),
					rs.getString("ticker"),rs.getString("name"),rs.getString("country"),rs.getString("address"));
			stmt.close();
			return company;
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
	}

	@Override
	public StockCompany findObject(String name) throws DataAccessException {
		LOGGER.trace("method: findObject("+name+")");
		String sql="select * from stock_company where idobject="+predictableObject.getObjId()+" and ticker='"+name.toUpperCase()+"'";
		try {
			PreparedStatement stmt = connection.prepareStatement(sql);
			ResultSet rs=stmt.executeQuery();
			rs.next();
			StockCompany company= new StockCompany(predictableObject.getObjId(),predictableObject.getObjName(),predictableObject.getType(),predictableObject.getVariableMap(), rs.getInt("idcompany"),
					rs.getString("ticker"),rs.getString("name"),rs.getString("country"),rs.getString("address"));
			stmt.close();
			return company;
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
	}
	
	public List<StockCompany> findAllObjects()
			throws DataAccessException {
		LOGGER.trace("method: findAllObjects()");
		String sql="select * from stock_index where idobject="+predictableObject.getObjId();
		List<StockCompany> companies = new Vector<StockCompany>();
		try {
			Statement stmt=connection.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			while(rs.next()){
				companies.add(new StockCompany(predictableObject.getObjId(),predictableObject.getObjName(),predictableObject.getType(),predictableObject.getVariableMap(),
						rs.getInt("idcompany"),rs.getString("ticker"),rs.getString("name"),rs.getString("country"),rs.getString("address")));
			}
			LOGGER.info("wyszedlem "+ companies.size());
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
		return companies;
	}

	@Override
	public Vector<String> findAllNamesOfObjects()
			throws DataAccessException {
		LOGGER.trace("method: findAllNamesOfObjects()");
		String sql="select name from stock_index where idobject="+predictableObject.getObjId();
		Vector<String> indexes = new Vector<String>();
		try {
			Statement stmt=connection.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			while(rs.next()){
				indexes.add(rs.getString("name"));
			}
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
		return indexes;
	}

	@Override
	public void batchSetVariablesData(BufferedReader reader,
			int objId, String method) throws DataAccessException, IOException {
		LOGGER.trace("method: batchSetCompanyVariablesDateValues("+reader+","+objId+","+ method+")");
		HashMap<String,PredictableObject.Variable> map = predictableObject.getVariableMap();
		if(method.contentEquals(companyHistoricalDataYahoo)){
			LOGGER.debug("batch method: " +companyHistoricalDataYahoo);
			String sqlHigh = "Insert into Company_Variables_Date(idCompany,idObject,idElement,var_date,value)" +
					"values ("+objId+","+predictableObject.getObjId()+","+map.get("HIGH").getVariableId()+",?,?)";
			String sqlLow = "Insert into Company_Variables_Date(idCompany,idObject,idElement,var_date,value)" +
					"values ("+objId+","+predictableObject.getObjId()+","+map.get("LOW").getVariableId()+",?,?)";
			String sqlClose = "Insert into Company_Variables_Date(idCompany,idObject,idElement,var_date,value)" +
					"values ("+objId+","+predictableObject.getObjId()+","+map.get("CLOSE").getVariableId()+",?,?)";
			String sqlOpen = "Insert into Company_Variables_Date(idCompany,idObject,idElement,var_date,value)" +
					"values ("+objId+","+predictableObject.getObjId()+","+map.get("OPEN").getVariableId()+",?,?)";
			String line;
			try {
				PreparedStatement stHigh = connection.prepareStatement(sqlHigh);
				PreparedStatement stLow = connection.prepareStatement(sqlLow);
				PreparedStatement stClose = connection.prepareStatement(sqlClose);
				PreparedStatement stOpen = connection.prepareStatement(sqlOpen);
				while ((line = reader.readLine()) != null) {
					String[] yahooStockInfo = line.split(",");
					if(yahooStockInfo[0].contentEquals("Date"))
						continue;
					else{
						stHigh.setDate(1, java.sql.Date.valueOf(yahooStockInfo[0]));
						stHigh.setFloat(2, Float.valueOf(yahooStockInfo[2]));
						stHigh.addBatch();
						stLow.setDate(1, java.sql.Date.valueOf(yahooStockInfo[0]));
						stLow.setFloat(2, Float.valueOf(yahooStockInfo[3]));
						stLow.addBatch();
						stOpen.setDate(1, java.sql.Date.valueOf(yahooStockInfo[0]));
						stOpen.setFloat(2, Float.valueOf(yahooStockInfo[1]));
						stOpen.addBatch();
						stClose.setDate(1, java.sql.Date.valueOf(yahooStockInfo[0]));
						stClose.setFloat(2, Float.valueOf(yahooStockInfo[4]));
						stClose.addBatch();
					}
					
				}
				stHigh.executeBatch();
				stHigh.close();
				stLow.executeBatch();
				stLow.close();
				stOpen.executeBatch();
				stOpen.close();
				stClose.executeBatch();
				stClose.close();
				
			} catch (SQLException e) {
				LOGGER.error("Couldn't create or execute batch statement "+sqlHigh +" or " +sqlLow+" or "+
			sqlClose+" or "+ sqlOpen, e);
				throw new DataAccessException("Couldn't create or execute batch statement "+sqlHigh +" or " +sqlLow+" or "+
						sqlClose+" or "+ sqlOpen,e);
			} catch (IOException e) {
				LOGGER.error("Coludn't read source:" + reader, e);
				throw e;
			}

			
		}

		
		
	}

	@Override
	public int[] getObjectVariableLastDate(int objId, int varId)
			throws DataAccessException {
		LOGGER.trace("method:  getObjectVariableLastDate("+objId+","+varId+")");
		String sql="select to_char(var_date,'YYYY') year, to_char(var_date,'ddd') day_of_year "+ 
   "from ( select var_date from company_variables_date"+
           " where idcompany="+objId+
           " and idobject="+predictableObject.getObjId()+
           " and idelement="+varId+
           " order by var_date desc ) "+
   "where rownum <= 1";
		int[] date =new int[2];
		try {
			Statement stmt=connection.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			while(rs.next()==true){
				date[0]=Integer.valueOf(rs.getString("year"));
				date[1]=Integer.valueOf(rs.getString("day_of_year"));
			}
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
		return date;
	}

	@Override
	public void clearVariablesData(int objId) throws DataAccessException {
		LOGGER.trace("method: clearVariablesData("+objId+")");
		String sql="delete from company_variables_date where idobject="+predictableObject.getObjId()+
				" and idcompany="+objId;
		String sql2="delete from company_variables_date_pred where idobject="+predictableObject.getObjId()+
				" and idcompany="+objId;
		try {
			Statement stmt=connection.createStatement();
			stmt.executeUpdate(sql);
			stmt.executeUpdate(sql2);
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql+" and "+sql2, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
		
	}

	@Override
	public String getSpecificObjectName() {
		LOGGER.trace("method: getSpecificObjectName()" );
		return specificObjectName;
	}

	public void createStockCompany( String ticker, String name,
			String country, String address) throws DataAccessException {
		LOGGER.trace("method: createStockCompany("+ticker+","+name+","+
			country+","+address+")) ");
		String sql="Insert into stock_company(idCompany,idObject,ticker,name,country,address) " +
				"values(stock_company_seq.nextval,"+predictableObject.getObjId()+","+ticker.toUpperCase()+","
				+name.toUpperCase()+","+country.toUpperCase()+","+address.toUpperCase()+")";
		try {
			Statement stmt = connection.createStatement();
			stmt.executeQuery(sql);
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
		
	}

	@Override
	public void createHistoricalDataFile(int objId, String variableName,
			Calendar startDate, Calendar endDate) {
		// TODO Auto-generated method stub
		
	}
}
